const SqliteUtil = appRequire('modules/SqliteUtil');
const PageResultVo = appRequire('vo/PageResultVo.js');
class SysUserSqliteDao {
  constructor() {

  }
  static async create() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    await db.run(`CREATE TABLE "sys_user" (
      "id" VARCHAR(64) NOT NULL,
      "nick_name" VARCHAR(64) DEFAULT '',
      "username" VARCHAR(64)  NOT NULL UNIQUE DEFAULT '',
      "password" VARCHAR(64) DEFAULT '',
      "user_icon" VARCHAR(255) DEFAULT '',
      "short_phone_num" VARCHAR(64) DEFAULT '',
      "phone_num" VARCHAR(32) DEFAULT '',
      "email" VARCHAR(255) DEFAULT '',
      "sys_user_role_ids" VARCHAR(2048) DEFAULT '',
      "remarks" VARCHAR(255) DEFAULT '',
      "create_time" VARCHAR(255) DEFAULT '',
      "update_time" VARCHAR(255) DEFAULT '',
      "is_used" VARCHAR(10) DEFAULT '0',
      "is_hide" VARCHAR(10) DEFAULT '0',
      "sort" integer DEFAULT 0,
      PRIMARY KEY ("id")
    );`);
    await db.close();
  }
  static async saveOne(user) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`insert into sys_user
          (id,nick_name,username,password,phone_num,email,remarks,create_time,update_time,sort) 
          values(?,?,?,?,?,?,?,?,?,?)`,
        [user.id, user.nickName, user.username, user.password, user.phoneNum, user.email,user.remarks,user.createTime, user.updateTime,user.sort]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async updateOne(user) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`update sys_user 
        set nick_name=?,password=?,user_icon=?,phone_num=?,short_phone_num=?,email=?,sys_user_role_ids=?,remarks=?,update_time=?,is_used=?,is_hide=?,sort=? where id = ?`,
      [user.nickName, user.password, user.userIcon, user.phoneNum, user.shortPhoneNum, user.email, user.roleIds,user.remarks, user.updateTime, user.isUsed, user.isHide, user.sort,user.id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      await db.run(`delete from sys_user where id = ?`,
      [id]);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async deleteMany(ids) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      console.log(ids);
      let sqlWhere = 'where id in (';
      for(let i = 0;i<ids.length;i++){
        if(i!=ids.length-1){
          sqlWhere+=`'${ids[i]}',`
        }else{
          sqlWhere+=`'${ids[i]}')`
        }
      }
      let sql = `delete from sys_user ${sqlWhere}`;
      console.log(sql);
      await db.run(sql);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findUserAuthItem(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let roleIdsObj = await db.get(`select sys_user_role_ids as roleIds from sys_user where id = ?`,[id]);
      let roleIds = roleIdsObj.roleIds.split(",");
      roleIds = roleIds.map(function(val){
        return `'${val}'`
      });
      roleIds = roleIds.join(",");
      let sql = `select sys_role_auth_ids as authIds from sys_role where id in (${roleIds})`
      let authIdsObj = await SqliteUtil.each(db,sql);
      let authIds = authIdsObj.map(function(val){
        return val.authIds;
      });
      let authIdsStr ="";
      authIds.forEach(function(val,index){
        if(index!=authIds.length-1){
          authIdsStr+=val+","
        }else{
          authIdsStr+=val
        }
      });
      let authIdsArr = authIdsStr.split(",");
      let authIdsSet = new Set();
      authIdsArr.forEach(function(val){
        authIdsSet.add(val);
      });
      authIds = Array.from(authIdsSet);
      authIds = authIds.map(function(val){
        return `'${val}'`
      });
      authIds = authIds.join(",");
      console.log(authIds);
      sql = `select auth_url as authUrl from sys_auth where auth_url<>'' and id in (${authIds})`
      let authUrlsObj = await SqliteUtil.each(db,sql);
      let authUrls = authUrlsObj.map(function(val){
        return val.authUrl;
      });
      let data ={};
      data.authUrls = authUrls;
      sql = `select sa.auth_name as authName,sa.auth_url as authUrl,sa.auth_icon as authIcon,
      sa.id,sa.pid 
      from sys_auth sa left join sys_auth_type st on sa.auth_type=st.id where st.type_name='系统菜单' and sa.id in (${authIds}) order by sa.sort desc`
      let authMenus = await SqliteUtil.each(db,sql);
      data.authMenus = authMenus;
      // console.log(data);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOneByUsernameAndPassword(user) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select 
      id,username,password 
      from sys_user where username=? and password=?`,[user.username,user.password]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findOne(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select id,username,nick_name as nickName,phone_num as phoneNum,sys_user_role_ids as roleIds,email,remarks from sys_user where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findById(id) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await db.get(`select * from sys_user where id=?`,[id]);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findAll() {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let data = await SqliteUtil.each(db,`select * from sys_user`);
      return data;
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }
  }
  static async findByPage(page) {
    let db = await SqliteUtil.open(appConfig.dbPath);
    try {
      let sqlWhere = '';
      let sqlParams = [(page.pageNum-1)*page.pageSize,page.pageSize];
      if(page.searchParams&&page.searchParams.username){
        sqlWhere +='where username=?'
        sqlParams.unshift(page.searchParams.username);
      }
      let result = await db.get(`select count(*) as totalNum from sys_user ${sqlWhere}`);
      let count = result.totalNum;
      let data = await SqliteUtil.each(db,`select id,username,nick_name as nickName,phone_num as phoneNum,sort from sys_user ${sqlWhere} order by create_time desc limit ?,?`,sqlParams);
      //加了await 会等待data拼装完成
      return PageResultVo.success('success',count,data);
    } catch (error) {
      throw new Error(error);
    } finally {
      await db.close();
    }   
  }
}
module.exports = SysUserSqliteDao;